

Task 1: Create a test database by using SQL PowerShell.

1. On NYC-SQL1, on the Start menu, click Run, type cmd, and
   then click OK. The Administrator: C:\Windows\system
   32\cmd.exe window appears.
2. At the command prompt, type sqlps, and then press Enter.
3. At the SQL Server PowerShell prompt, run cd
   SQL\localhost\default\databases.
4. At the database prompt, run Get-ChildItem.
5. At the database prompt, type each of the following commands,
   and then press Enter.

$NewDB=New-Object
Microsoft.SqlServer.Management.Smo.Database
$NewDB.Parent=(Get-Item ..)
$NewDB.Name="PS_Test"
$NewDB.Create()

********************************************************************

Task 2: Configure the database compatibility level.

1. At the database prompt, run the cd ps_test command.
2. At the ps_test prompt, run the following commands.

$MyDB=Get-Item .
$MyDB.CompatibilityLevel
$MyDB.CompatibilityLevel="Version80"
$MyDB.Alter()

********************************************************************

Task 3: Verify the database configuration.

1. At the ps_test prompt, run the following command.

$MyDB.CompatibilityLevel

********************************************************************

Task 4: Drop the test database.
1. At the ps_test prompt, run the following commands.

$MyDB.Drop()
cd ..
Get-ChildItem

2. Close the Administrator: C:\Windows\system 32\cmd.exe  sqlps
window.

********************************************************************
Exercise 4: Creating SQL Server PowerShell Scripts

Task 1: Add a SQL Server PowerShell script to create a SQL
Server Agent job.

1. On the Start menu, point to All Programs, click Accessories,
and then click Notepad. The Notepad window appears.
2. In the Notepad window, type the following SQL Server PowerShell
script.

$server=Get-Item \SQL\localhost\default
$job=New-Object
Microsoft.SqlServer.Management.Smo.Agent.Job
$Job.parent=$server.JobServer
$job.Name="Backup System Database job"
$job.Create()

********************************************************************

Task 2: Add the SQL Server PowerShell script to create a SQL
Server Agent job step.

1. In the Notepad window, type the following SQL Server PowerShell
script at the end of the existing script.

$masterstep=New-Object
Microsoft.SqlServer.Management.Smo.Agent.JobStep
$masterstep.parent=$job
$masterstep.Name="backup master"
$masterstep.SubSystem="TransactSQL"
$masterstep.Command="BACKUP DATABASE master TO
DISK='D:\Labfiles\Mod01\master.bak'"
$masterstep.OnSuccessAction = "GoToNextStep"
$masterstep.Create()
$modelstep=New-Object
Microsoft.SqlServer.Management.Smo.Agent.JobStep

$modelstep.parent=$job
$modelstep.Name="backup model"
$modelstep.SubSystem="TransactSQL"
$modelstep.Command="BACKUP DATABASE model TO
DISK='D:\Labfiles\Mod01\model.bak'"
$modelstep.OnSuccessAction = "GoToNextStep"
$modelstep.Create()

********************************************************************

Task 3: Save the SQL PowerShell script.
1. In the Notepad window, click File, and then click Save.
2. In the File name box, type

D:\Labfiles\Mod01\create_job.ps1, and then click Save.
3. Close create_job.ps1-Notepad.

********************************************************************

Task 4: Start SQL PowerShell and run the script.
1. On the Start menu, click Run, type cmd, and then click OK. The
Administrator: C:\Windows\system 32\cmd.exe window appears.
2. At the Administrator: C:\Windows\system 32\cmd.exe, type
sqlps, and then press Enter.
3. At the SQL Powershell prompt, run the
D:\Labfiles\Mod01\create_job.ps1.